package cn.javaex.hgo.action.system.service.hgo_table_info;

import java.io.IOException;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import cn.javaex.hgo.action.system.dao.hgo_table_column_info.IHgoTableColumnInfoDAO;
import cn.javaex.hgo.action.system.dao.hgo_table_info.IHgoTableInfoDAO;
import cn.javaex.hgo.action.system.service.code_generate.CodeGenerateService;
import cn.javaex.hgo.action.system.view.HgoTableColumnInfo;
import cn.javaex.hgo.action.system.view.HgoTableInfo;
import cn.javaex.hgo.config.constant.HgoErrorMsg;
import cn.javaex.hgo.config.exception.HgoException;

@Service("HgoTableInfoService")
public class HgoTableInfoService {
	
	@Autowired
	private IHgoTableInfoDAO iHgoTableInfoDAO;
	@Autowired
	private IHgoTableColumnInfoDAO iHgoTableColumnInfoDAO;
	@Autowired
	private CodeGenerateService codeGenerateService;
	
	/**
	 * 查询表列表
	 * @param param
	 * @return
	 */
	public List<HgoTableInfo> list(Map<String, Object> param) {
		return iHgoTableInfoDAO.list(param);
	}

	/**
	 * 根据主键，查询表信息
	 * @param id
	 * @return
	 */
	public HgoTableInfo selectById(String id) {
		return iHgoTableInfoDAO.selectById(id);
	}

	/**
	 * 保存表信息
	 * @param hgoTableInfo
	 * @throws HgoException
	 */
	public void save(HgoTableInfo hgoTableInfo) throws HgoException {
		// 1.0 校验表名是否重复
		int count = iHgoTableInfoDAO.countByTableName(hgoTableInfo.getTableName(), hgoTableInfo.getId());
		if (count>0) {
			throw new HgoException(HgoErrorMsg.ERROR_200001);
		}
		
		Date now = new Date();
		hgoTableInfo.setUpdateTime(now);
		
		// 2.0 新增或更新
		if (StringUtils.isEmpty(hgoTableInfo.getId())) {
			// 新增
			hgoTableInfo.setCreateTime(now);
			iHgoTableInfoDAO.insert(hgoTableInfo);
		} else {
			// 更新
			iHgoTableInfoDAO.update(hgoTableInfo);
		}
	}

	/**
	 * 删除表
	 * @param id
	 */
	public void deleteById(String id) {
		// 1.0 删除字段表中的数据
		iHgoTableColumnInfoDAO.deleteByTableId(id);
		
		// 2.0 删除表中的数据
		iHgoTableInfoDAO.deleteById(id);
	}

	/**
	 * 同步数据库表
	 * @param idArr 表主键数组
	 * @param synchronousType 同步类型（1：普通同步；2：强制同步（先删除已存在的表，再创建））
	 */
	public void synchronousToDatabase(String[] idArr, String synchronousType) {
		for (String id : idArr) {
			// 表信息
			HgoTableInfo hgoTableInfo = iHgoTableInfoDAO.selectById(id);
			// 表字段信息
			List<HgoTableColumnInfo> columnList = iHgoTableColumnInfoDAO.listByTableId(id);
			
			// 1.0 校验表在数据库中是否已存在
			int count = iHgoTableInfoDAO.isTableExist(hgoTableInfo.getTableName());
			if ("1".equals(synchronousType)) {
				// 2.0 普通同步
				if (count==0) {
					// 2.1 直接创建表
					createTable(hgoTableInfo.getTableName(), columnList);
				} else {
					// 2.1 获取该表在数据库中的所有字段
					Set<String> dbColumnSet = iHgoTableInfoDAO.listColumnByTableName(hgoTableInfo.getTableName());
					// 2.2 遍历用户自定义的字段
					StringBuffer sb = new StringBuffer();
					for (HgoTableColumnInfo hgoTableColumnInfo : columnList) {
						// 2.3 判断该字段是否已废弃（1：启用， 0：废弃）
						if (hgoTableColumnInfo.getStatus()==1) {
							// 2.4 判断字段是否已存在
							if (!dbColumnSet.contains(hgoTableColumnInfo.getColumnName())) {
								sb.append(" ALTER TABLE `"+hgoTableInfo.getTableName()+"` ADD `"+hgoTableColumnInfo.getColumnName()+"` ");
								sb.append(appendColumnStr(hgoTableColumnInfo));
								sb.append(";");
							}
						}
					}
					if (StringUtils.isNotEmpty(sb.toString())) {
						// 3.0 执行SQL语句
						iHgoTableInfoDAO.alter(sb.toString());
					}
				}
			} else if ("2".equals(synchronousType)) {
				// 2.0 强制同步
				// 2.1 先删除表
				iHgoTableInfoDAO.alter("DROP TABLE IF EXISTS `"+hgoTableInfo.getTableName()+"`;");
				// 2.2 再创建表
				createTable(hgoTableInfo.getTableName(), columnList);
			}
		}
	}

	/**
	 * 创建表
	 * @param tableName 表名
	 * @param columnList 字段集合
	 */
	private void createTable(String tableName, List<HgoTableColumnInfo> columnList) {
		// 1.0 定义主键变量
		String PRIMARY_KEY = "";
		
		// 2.0 构建字符串
		StringBuffer sb = new StringBuffer();
		sb.append(" CREATE TABLE `"+tableName+"` ( ");
		for (HgoTableColumnInfo hgoTableColumnInfo : columnList) {
			// 判断该字段是否已废弃（1：启用， 0：废弃）
			if (hgoTableColumnInfo.getStatus()==1) {
				// 判断该字段是不是主键
				if (hgoTableColumnInfo.getIsPrimaryKey()==1) {
					// 主键
					if ("".equals(PRIMARY_KEY)) {
						PRIMARY_KEY = "`"+hgoTableColumnInfo.getColumnName()+"`";
					} else {
						PRIMARY_KEY += ",`"+hgoTableColumnInfo.getColumnName()+"`";
					}
					// 主键自增或UUID
					if ("int".equals(hgoTableColumnInfo.getType())) {
						// 主键自增
						sb.append(" `"+hgoTableColumnInfo.getColumnName()+"` int(11) NOT NULL AUTO_INCREMENT ");
					} else if ("smallint".equals(hgoTableColumnInfo.getType())) {
						// 主键自增
						sb.append(" `"+hgoTableColumnInfo.getColumnName()+"` smallint(4) NOT NULL AUTO_INCREMENT ");
					} else if ("mediumint".equals(hgoTableColumnInfo.getType())) {
						// 主键自增
						sb.append(" `"+hgoTableColumnInfo.getColumnName()+"` mediumint(8) NOT NULL AUTO_INCREMENT ");
					} else {
						sb.append(" `"+hgoTableColumnInfo.getColumnName()+"` varchar("+hgoTableColumnInfo.getLength()+") NOT NULL ");
					}
					// 是否有注释
					if (StringUtils.isNotBlank(hgoTableColumnInfo.getChsname())) {
						sb.append(" COMMENT '"+hgoTableColumnInfo.getChsname()+"' ");
					}
					sb.append(",");
				} else {
					// 非主键
					sb.append(" `"+hgoTableColumnInfo.getColumnName()+"` ");
					sb.append(appendColumnStr(hgoTableColumnInfo));
					sb.append(",");
				}
			}
		}
		
		// 2.1 判断是否存在主键
		if (!"".equals(PRIMARY_KEY)) {
			sb.append(" PRIMARY KEY ("+PRIMARY_KEY+") ");
		} else {
			// 删除最后一个,
			sb.deleteCharAt(sb.length() - 1);
		}
		sb.append(")");
		
		// 3.0 执行SQL语句
		iHgoTableInfoDAO.alter(sb.toString());
		
		// 4.0 设置MySQL引擎为InnoDB
		if (PRIMARY_KEY.indexOf(",")==-1) {
			iHgoTableInfoDAO.alter("ALTER TABLE `"+tableName+"` ENGINE=InnoDB;");
		}
	}
	
	/**
	 * 追加字段字符串
	 * @param hgoTableColumnInfo
	 * @return
	 */
	private String appendColumnStr(HgoTableColumnInfo hgoTableColumnInfo) {
		StringBuffer sb = new StringBuffer();
		// 类型
		if ("int".equals(hgoTableColumnInfo.getType())) {
			sb.append(" int("+hgoTableColumnInfo.getLength()+") ");
		} else if ("smallint".equals(hgoTableColumnInfo.getType())) {
			sb.append(" smallint("+hgoTableColumnInfo.getLength()+") ");
		} else if ("mediumint".equals(hgoTableColumnInfo.getType())) {
			sb.append(" mediumint("+hgoTableColumnInfo.getLength()+") ");
		} else if ("double".equals(hgoTableColumnInfo.getType())) {
			sb.append(" double ");
		} else if ("varchar".equals(hgoTableColumnInfo.getType())) {
			sb.append(" varchar("+hgoTableColumnInfo.getLength()+") ");
		} else if ("date".equals(hgoTableColumnInfo.getType())) {
			sb.append(" date ");
		} else if ("datetime".equals(hgoTableColumnInfo.getType())) {
			sb.append(" datetime ");
		} else if ("tinyint".equals(hgoTableColumnInfo.getType())) {
			sb.append(" tinyint(1) ");
		} else if ("decimal".equals(hgoTableColumnInfo.getType())) {
			sb.append(" decimal("+hgoTableColumnInfo.getLength()+","+hgoTableColumnInfo.getPoint()+") ");
		} else if ("text".equals(hgoTableColumnInfo.getType())) {
			sb.append(" text ");
		}
		// 是否不为空
		if (hgoTableColumnInfo.getIsNotNull()==1) {
			sb.append(" NOT NULL ");
		}
		// 是否有默认值
		if (StringUtils.isNotBlank(hgoTableColumnInfo.getDefaultValue())) {
			sb.append(" DEFAULT '"+hgoTableColumnInfo.getDefaultValue()+"' ");
		} else {
			if (hgoTableColumnInfo.getIsNotNull()==0) {
				sb.append(" DEFAULT NULL ");
			}
		}
		// 是否有注释
		if (StringUtils.isNotBlank(hgoTableColumnInfo.getChsname())) {
			sb.append(" COMMENT '"+hgoTableColumnInfo.getChsname()+"' ");
		}
		
		return sb.toString();
	}

	/**
	 * 数据库表列表
	 * @param param
	 * @return
	 */
	public List<String> listTableFromDatabase(Map<String, Object> param) {
		return iHgoTableInfoDAO.listTableFromDatabase(param);
	}

	/**
	 * 从数据库导出表信息
	 * @param tableNameArr 表名数组
	 */
	public void synchronousFromDatabase(String[] tableNameArr) {
		Date now = new Date();
		
		// 遍历所选的表
		for (String tableName : tableNameArr) {
			// 1.0 判断所选表在系统中是否已存在（防止因系统反应慢而造成的误选）
			int count = iHgoTableInfoDAO.countByTableName(tableName, null);
			if (count==0) {
				// 2.0 查询表的字段信息
				List<Map<String, String>> list = iHgoTableInfoDAO.listColumnInfoFromDatabaseByTableName(tableName);
				if (list!=null && list.isEmpty()==false) {
					// 3.0 插入表 hgo_table_info
					HgoTableInfo hgoTableInfo = new HgoTableInfo();
					hgoTableInfo.setTableName(tableName);
					hgoTableInfo.setStatus(1);
					hgoTableInfo.setCreateTime(now);
					hgoTableInfo.setUpdateTime(now);
					iHgoTableInfoDAO.insert(hgoTableInfo);
					
					// 4.0 遍历数据库中表的字段属性
					int index = 1;
					for (Map<String, String> map : list) {
						HgoTableColumnInfo hgoTableColumnInfo = new HgoTableColumnInfo();
						hgoTableColumnInfo.setTableId(hgoTableInfo.getId());	// 表id
						hgoTableColumnInfo.setColumnName(map.get("COLUMN_NAME"));	// 字段名
						hgoTableColumnInfo.setChsname(map.get("COLUMN_COMMENT"));	// 字段表述
						hgoTableColumnInfo.setType(map.get("DATA_TYPE"));			// 字段类型
						// 长度和小数点
						String columnType = map.get("COLUMN_TYPE");
						if (columnType.indexOf("(")==-1) {
							hgoTableColumnInfo.setLength(0);
							hgoTableColumnInfo.setPoint(0);
						} else {
							// 获取()内的内容
							columnType = columnType.substring(columnType.indexOf("(")+1, columnType.length()-1);
							if (columnType.indexOf(",")==-1) {
								hgoTableColumnInfo.setLength(Integer.valueOf(columnType));
								hgoTableColumnInfo.setPoint(0);
							} else {
								// decimal类型
								String[] arr = columnType.split(",");
								hgoTableColumnInfo.setLength(Integer.valueOf(arr[0]));
								hgoTableColumnInfo.setPoint(Integer.valueOf(arr[1]));
							}
						}
						// 主键
						if ("PRI".equals(map.get("COLUMN_KEY").toUpperCase())) {
							hgoTableColumnInfo.setIsPrimaryKey(1);
						} else {
							hgoTableColumnInfo.setIsPrimaryKey(0);
						}
						// 是否不为空
						if ("NO".equals(map.get("IS_NULLABLE").toUpperCase())) {
							hgoTableColumnInfo.setIsNotNull(1);
						} else {
							hgoTableColumnInfo.setIsNotNull(0);
						}
						// 默认值
						if (StringUtils.isNotBlank(map.get("COLUMN_DEFAULT"))) {
							hgoTableColumnInfo.setDefaultValue(map.get("COLUMN_DEFAULT"));
						}
						hgoTableColumnInfo.setStatus(1);
						hgoTableColumnInfo.setSort(index++);
						hgoTableColumnInfo.setCreateTime(now);
						hgoTableColumnInfo.setUpdateTime(now);
						iHgoTableColumnInfoDAO.insert(hgoTableColumnInfo);
					}
				}
			}
		}
	}
	
	/**
	 * 代码生成
	 * @param param
	 * @param idArr
	 * @throws IOException
	 * @throws HgoException
	 */
	public void codeGenerate(Map<String, Object> param) throws IOException, HgoException {
		// 1.0 获取部分设定值
		String[] idArr = (String[])param.get("idArr");	// 表id数组
		String[] codeTypeArr = (String[])param.get("codeTypeArr");	// 生成代码种类
		
		if (codeTypeArr==null || codeTypeArr.length==0) {
			throw new HgoException(HgoErrorMsg.ERROR_200003);
		}
		
		// 2.0 遍历生成的表
		for (String id : idArr) {
			// 3.0 获取表信息
			// 3.1 表信息
			HgoTableInfo hgoTableInfo = iHgoTableInfoDAO.selectById(id);
			// 3.2 表字段信息
			List<HgoTableColumnInfo> columnList = iHgoTableColumnInfoDAO.listByTableId(id);
			
			// 4.0 根据生成类型，生成不同文件
			for (String codeType : codeTypeArr) {
				if (codeType.equals("controller")) {
					codeGenerateService.generateController(param, hgoTableInfo, columnList);
				} else if (codeType.equals("service")) {
					codeGenerateService.generateService(param, hgoTableInfo, columnList);
				} else if (codeType.equals("dao")) {
					codeGenerateService.generateDaoJava(param, hgoTableInfo, columnList);
					codeGenerateService.generateDaoXml(param, hgoTableInfo, columnList);
				} else if (codeType.equals("view")) {
					codeGenerateService.generateView(param, hgoTableInfo, columnList);
				} else if (codeType.equals("jsp")) {
					codeGenerateService.generateJspList(param, hgoTableInfo, columnList);
					codeGenerateService.generateJspEdit(param, hgoTableInfo, columnList);
				}
			}
		}
	}

}